﻿using System.Data;
using System.Configuration;
using System.Web;
using System.IO;
using System.Text;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Collections;

     public class NPOIExcelHelper
     {
         /// <summary>
         /// DataTable导出到Excel文件
         /// </summary>
         /// <param name="dtSource">源DataTable</param>
         /// <param name="strHeaderText">表头文本</param>
         /// <param name="strFileName">保存位置</param>
         public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName)
         {
             using (MemoryStream ms = DataTableToExcel(dtSource, strHeaderText))
             {
                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                 {
                     byte[] data = ms.ToArray();
                     fs.Write(data, 0, data.Length);
                     fs.Flush();
                 }
             }
         }
         /// <summary>
         /// DataGridView导出到Excel文件
         /// </summary>
         /// <param name="dtSource">源DataTGridview</param>
         /// <param name="strHeaderText">表头文本</param>
         /// <param name="strFileName">保存位置</param>
         public static void DataGridViewToExcel(DataGridView myDgv, string strHeaderText, string strFileName)
         {
             using (MemoryStream ms = DataGridViewToExcel(myDgv, strHeaderText))
             {
                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                 {
                     byte[] data = ms.ToArray();
                     fs.Write(data, 0, data.Length);
                     fs.Flush();
                 }
             }
         }
 
         /// <summary>
         /// DataTable导出到Excel的MemoryStream
         /// </summary>
         /// <param name="dtSource">源DataTable</param>
         /// <param name="strHeaderText">表头文本</param>
         public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
         {
             HSSFWorkbook workbook = new HSSFWorkbook();
             HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
 
             #region 右击文件 属性信息
             {
                 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                 dsi.Company = "NPOI";
                 workbook.DocumentSummaryInformation = dsi;
 
                 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                 si.Author = "文件作者信息"; //填加xls文件作者信息
                 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
                 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
                 si.Comments = "作者信息"; //填加xls文件作者信息
                 si.Title = "标题信息"; //填加xls文件标题信息
                 si.Subject = "主题信息";//填加文件主题信息
                 si.CreateDateTime = System.DateTime.Now;
                 workbook.SummaryInformation = si;
             }
             #endregion

             HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
             HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
 
             //取得列宽
             int[] arrColWidth = new int[dtSource.Columns.Count];
             foreach (DataColumn item in dtSource.Columns)
             {
                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
             }
             for (int i = 0; i < dtSource.Rows.Count; i++)
             {
                 for (int j = 0; j < dtSource.Columns.Count; j++)
                 {
                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                     if (intTemp > arrColWidth[j])
                     {
                         arrColWidth[j] = intTemp;
                     }
                 }
             } 
             int rowIndex = 0; 
             foreach (DataRow row in dtSource.Rows)
             {
                 #region 新建表，填充表头，填充列头，样式
                 if (rowIndex == 65535 || rowIndex == 0)
                 {
                     if (rowIndex != 0)
                     {
                         sheet = (HSSFSheet)workbook.CreateSheet();
                     }
 
                     #region 表头及样式
                     {
                         HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                         headerRow.HeightInPoints = 25;
                         headerRow.CreateCell(0).SetCellValue(strHeaderText);

                         HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                       //  headStyle.Alignment = CellHorizontalAlignment.CENTER;
                         HSSFFont font = (HSSFFont)workbook.CreateFont();
                         font.FontHeightInPoints = 20;
                         font.Boldweight = 700;
                         headStyle.SetFont(font);
                         headerRow.GetCell(0).CellStyle = headStyle;
                        // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                         //headerRow.Dispose();
                     }
                     #endregion
 
 
                     #region 列头及样式
                     {
                         HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); 
                         HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                         //headStyle.Alignment = CellHorizontalAlignment.CENTER;
                         HSSFFont font = (HSSFFont)workbook.CreateFont();
                         font.FontHeightInPoints = 10;
                         font.Boldweight = 700;
                         headStyle.SetFont(font); 
                         foreach (DataColumn column in dtSource.Columns)
                         {
                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
 
                             //设置列宽
                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); 
                         }
                        // headerRow.Dispose();
                     }
                     #endregion
 
                     rowIndex = 2;
                 }
                 #endregion
 
 
                 #region 填充内容
                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                 foreach (DataColumn column in dtSource.Columns)
                 {
                     HSSFCell newCell =(HSSFCell) dataRow.CreateCell(column.Ordinal);
 
                     string drValue = row[column].ToString();
 
                     switch (column.DataType.ToString())
                     {
                         case "System.String"://字符串类型
                             newCell.SetCellValue(drValue);
                             break;
                         case "System.DateTime"://日期类型
                            System.DateTime dateV;
                            System.DateTime.TryParse(drValue, out dateV);
                             newCell.SetCellValue(dateV);
 
                             newCell.CellStyle = dateStyle;//格式化显示
                             break;
                         case "System.Boolean"://布尔型
                             bool boolV = false;
                             bool.TryParse(drValue, out boolV);
                             newCell.SetCellValue(boolV);
                             break;
                         case "System.Int16"://整型
                         case "System.Int32":
                         case "System.Int64":
                         case "System.Byte":
                             int intV = 0;
                             int.TryParse(drValue, out intV);
                             newCell.SetCellValue(intV);
                             break;
                         case "System.Decimal"://浮点型
                         case "System.Double":
                             double doubV = 0;
                             double.TryParse(drValue, out doubV);
                             newCell.SetCellValue(doubV);
                             break;
                         case "System.DBNull"://空值处理
                             newCell.SetCellValue("");
                             break;
                         default:
                             newCell.SetCellValue("");
                             break;
                     }
 
                 }
                 #endregion
 
                 rowIndex++;
             } 
             using (MemoryStream ms = new MemoryStream())
             {
                 workbook.Write(ms);
                 ms.Flush();
                 ms.Position = 0;
 
                 //sheet.Dispose();
                 workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                 return ms;
             } 
         }
         /// <summary>
         /// DataTable导出到Excel的MemoryStream
         /// </summary>
         /// <param name="myDgv">源DataTable</param>
         /// <param name="strHeaderText">表头文本</param>
         public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText)
         {
             HSSFWorkbook workbook = new HSSFWorkbook();
             HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

             #region 右击文件 属性信息
             {
                 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                 dsi.Company = "NPOI";
                 workbook.DocumentSummaryInformation = dsi;

                 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                 si.Author = "文件作者信息"; //填加xls文件作者信息
                 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
                 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
                 si.Comments = "作者信息"; //填加xls文件作者信息
                 si.Title = "标题信息"; //填加xls文件标题信息
                 si.Subject = "主题信息";//填加文件主题信息
                 si.CreateDateTime = System.DateTime.Now;
                 workbook.SummaryInformation = si;
             }
             #endregion

             HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
             HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

             //取得列宽
             int[] arrColWidth = new int[myDgv.Columns.Count];
             foreach (DataGridViewColumn item in myDgv.Columns)
             {
                 arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
             }
             for (int i = 0; i < myDgv.Rows.Count; i++)
             {
                 for (int j = 0; j < myDgv.Columns.Count; j++)
                 {
                     int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
                     if (intTemp > arrColWidth[j])
                     {
                         arrColWidth[j] = intTemp;
                     }
                 }
             }
             int rowIndex = 0;
             foreach (DataGridViewRow row in myDgv.Rows)
             {
                 #region 新建表，填充表头，填充列头，样式
                 if (rowIndex == 65535 || rowIndex == 0)
                 {
                     if (rowIndex != 0)
                     {
                         sheet = (HSSFSheet)workbook.CreateSheet();
                     }

                     #region 表头及样式
                     {
                         HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                         headerRow.HeightInPoints = 25;
                         headerRow.CreateCell(0).SetCellValue(strHeaderText);

                         HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                         //  headStyle.Alignment = CellHorizontalAlignment.CENTER;
                         HSSFFont font = (HSSFFont)workbook.CreateFont();
                         font.FontHeightInPoints = 20;
                         font.Boldweight = 700;
                         headStyle.SetFont(font);
                         headerRow.GetCell(0).CellStyle = headStyle;
                         // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                         //headerRow.Dispose();
                     }
                     #endregion


                     #region 列头及样式
                     {
                         HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
                         HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                         //headStyle.Alignment = CellHorizontalAlignment.CENTER;
                         HSSFFont font = (HSSFFont)workbook.CreateFont();
                         font.FontHeightInPoints = 10;
                         font.Boldweight = 700;
                         headStyle.SetFont(font);
                         foreach (DataGridViewColumn column in myDgv.Columns)
                         {
                             headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
                             headerRow.GetCell(column.Index).CellStyle = headStyle;

                             //设置列宽
                             sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
                         }
                         // headerRow.Dispose();
                     }
                     #endregion

                     rowIndex = 2;
                 }
                 #endregion


                 #region 填充内容
                 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                 if (row.Index > 0)
                 {
                     foreach (DataGridViewColumn column in myDgv.Columns)
                     {
                         HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index);

                         string drValue = myDgv[column.Index, row.Index-1].Value+"";

                         switch (column.ValueType + "")
                         {
                             case "System.String"://字符串类型
                                 newCell.SetCellValue(drValue);
                                 break;
                             case "System.DateTime"://日期类型
                                 System.DateTime dateV;
                                 System.DateTime.TryParse(drValue, out dateV);
                                 newCell.SetCellValue(dateV);

                                 newCell.CellStyle = dateStyle;//格式化显示
                                 break;
                             case "System.Boolean"://布尔型
                                 bool boolV = false;
                                 bool.TryParse(drValue, out boolV);
                                 newCell.SetCellValue(boolV);
                                 break;
                             case "System.Int16"://整型
                             case "System.Int32":
                             case "System.Int64":
                             case "System.Byte":
                                 int intV = 0;
                                 int.TryParse(drValue, out intV);
                                 newCell.SetCellValue(intV);
                                 break;
                             case "System.Decimal"://浮点型
                             case "System.Double":
                                 double doubV = 0;
                                 double.TryParse(drValue, out doubV);
                                 newCell.SetCellValue(doubV);
                                 break;
                             case "System.DBNull"://空值处理
                                 newCell.SetCellValue("");
                                 break;
                             default:
                                 newCell.SetCellValue("");
                                 break;
                         }

                     }
                 }
                 else
                 { rowIndex--; }
                 #endregion

                 rowIndex++;
             }
             using (MemoryStream ms = new MemoryStream())
             {
                 workbook.Write(ms);
                 ms.Flush();
                 ms.Position = 0;

                 //sheet.Dispose();
                 workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                 return ms;
             }
         }
 
         
 
         /// <summary>读取excel
         /// 默认第一行为标头
         /// </summary>
         /// <param name="strFileName">excel文档路径</param>
         /// <returns></returns>
         public static DataTable Import(string strFileName)
         {
             DataTable dt = new DataTable();
 
             HSSFWorkbook hssfworkbook;
             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
             {
                 hssfworkbook = new HSSFWorkbook(file);
             }
             HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
             System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
 
             HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
             int cellCount = headerRow.LastCellNum;
 
             for (int j = 0; j < cellCount; j++)
             {
                 HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                 dt.Columns.Add(cell.ToString());
             }
 
             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
             {
                 HSSFRow row = (HSSFRow)sheet.GetRow(i);
                 DataRow dataRow = dt.NewRow();
 
                 for (int j = row.FirstCellNum; j < cellCount; j++)
                 {
                     if (row.GetCell(j) != null)
                         dataRow[j] = row.GetCell(j).ToString();
                 }
 
                 dt.Rows.Add(dataRow);
             }
             return dt;
         }

         //private string fileName = null; //文件名
         private IWorkbook workbook = null;
         //private 
         /// <summary>
         /// 将excel中的数据导入到DataTable中
         /// </summary>
         /// <param name="sheetName">excel工作薄sheet的名称</param>
         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
         /// <returns>返回的DataTable</returns>
         public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn)
         {
             ISheet sheet = null;
             DataTable data = new DataTable();
             int startRow = 0;
             FileStream fs = null;
             try
             {
                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                     workbook = new XSSFWorkbook(fs);
                 else if (fileName.IndexOf(".xls") > 0) // 2003版本
                     workbook = new HSSFWorkbook(fs);

                 if (sheetName != null)
                 {
                     sheet = workbook.GetSheet(sheetName);
                     if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                     {
                         sheet = workbook.GetSheetAt(0);
                     }
                 }
                 else
                 {
                     sheet = workbook.GetSheetAt(0);
                 }
                 if (sheet != null)
                 {
                     IRow firstRow = sheet.GetRow(0);
                     //int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                     int cellCount = 9;
                     if (isFirstRowColumn)
                     {
                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                         {
                             ICell cell = firstRow.GetCell(i);
                             if (cell != null)
                             {
                                 string cellValue = cell.StringCellValue;
                                 if (cellValue != null)
                                 {
                                     DataColumn column = new DataColumn(cellValue);
                                     data.Columns.Add(column);
                                 }
                             }
                         }
                         startRow = sheet.FirstRowNum + 1;
                     }
                     else
                     {
                         startRow = sheet.FirstRowNum;
                     }
                     startRow = 2;
                     //最后一列的标号
                     int rowCount = sheet.LastRowNum;
                     for (int i = startRow; i <= rowCount; ++i)
                     {
                         IRow row = sheet.GetRow(i);
                         if (row == null) continue; //没有数据的行默认是null　　　　　　　

                         DataRow dataRow = data.NewRow();
                         for (int j = row.FirstCellNum; j < cellCount; ++j)
                         {
                             if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                 dataRow[j] = row.GetCell(j).ToString();
                         }
                         data.Rows.Add(dataRow);
                     }
                 }

                 return data;
             }
             catch (Exception ex)
             {
                 MessageBox.Show("Exception: " + ex.Message);
                 return null;
             }
         }


         /// <summary>
         /// 获取excel内容
         /// </summary>
         /// <param name="filePath">excel文件路径</param>
         /// <returns></returns>
         public DataTable ExcelToDataTable2(string filePath, string sheetName)
         {
             DataTable dt = new DataTable();
             using (FileStream fsRead = System.IO.File.OpenRead(filePath))
             {
                 IWorkbook wk = null;
                 ISheet sheet = null;
                 //获取后缀名
                 string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                 //判断是否是excel文件
                 if (extension == ".xlsx" || extension == ".xls")
                 {
                     //判断excel的版本
                     if (extension == ".xlsx")
                     {
                         wk = new XSSFWorkbook(fsRead);
                     }
                     else
                     {
                         wk = new HSSFWorkbook(fsRead);
                     }
                     //获取第2个sheet
                     //sheet = wk.GetSheetAt(1);
                     sheet = workbook.GetSheetAt(1);
                     //if (sheetName != null)
                     //{
                     //    sheet = workbook.GetSheet(sheetName);
                     //    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                     //    {
                     //        sheet = workbook.GetSheetAt(0);
                     //    }
                     //}
                     //else
                     //{
                     //    sheet = workbook.GetSheetAt(0);
                     //}
                     //获取第一行
                     IRow headrow = sheet.GetRow(0);
                     //创建列
                     for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
                     {
                         //  DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
                         DataColumn datacolum = new DataColumn("F" + (i + 1));
                         dt.Columns.Add(datacolum);
                     }
                     //读取每行,从第二行起
                     for (int r = 1; r <= sheet.LastRowNum; r++)
                     {
                         bool result = false;
                         DataRow dr = dt.NewRow();
                         //获取当前行
                         IRow row = sheet.GetRow(r);
                         //读取每列
                         for (int j = 0; j < row.Cells.Count; j++)
                         {
                             ICell cell = row.GetCell(j); //一个单元格
                             dr[j] = GetCellValue(cell); //获取单元格的值
                             //全为空则不取
                             if (dr[j].ToString() != "")
                             {
                                 result = true;
                             }
                         }
                         if (result == true)
                         {
                             dt.Rows.Add(dr); //把每行追加到DataTable
                         }
                     }
                 }

             }
             return dt;
         }


         /// <summary>
         /// 根据Excel返回DataSet
         /// </summary>
         /// <param name="filePath">Excel文件地址</param>
         /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
         /// <returns>DataSet</returns>
         public DataSet GetDataSet(string filePath, int? sheetIndex = null)
         {
             DataSet ds = new DataSet();
            
             using (FileStream fsRead = new FileStream(filePath, FileMode.Open, FileAccess.Read))
             {
                 IWorkbook fileWorkbook=null;
                 string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                 if (extension == ".xlsx" || extension == ".xls")
                 {
                     //判断excel的版本
                     if (extension == ".xlsx")
                     {
                         fileWorkbook = new XSSFWorkbook(fsRead);
                     }
                     else
                     {
                         fileWorkbook = new HSSFWorkbook(fsRead);
                     }
                 }

                 for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
                 {
                     if (sheetIndex != null && sheetIndex != i)
                         continue;
                     DataTable dt = new DataTable();
                     ISheet sheet = fileWorkbook.GetSheetAt(i);

                     //表头
                     IRow header = sheet.GetRow(1);//sheet.FirstRowNum
                     List<int> columns = new List<int>();
                     for (int j = 0; j < header.LastCellNum; j++)
                     {
                         object obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell);
                         if (obj == null || obj.ToString() == string.Empty)
                         {
                             dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
                         }
                         else
                             dt.Columns.Add(new DataColumn(obj.ToString()));
                         columns.Add(j);
                     }
                     //数据
                     IEnumerator rows = sheet.GetEnumerator();
                     while (rows.MoveNext())
                     {
                        // int j = sheet.FirstRowNum + 1;
                         int j = 2;
                         DataRow dr = dt.NewRow();
                         bool hasValue = false;
                         foreach (int K in columns)
                         {
                             dr[K] = GetValueTypeForXLS(sheet.GetRow(K).GetCell(K) as HSSFCell);
                             if (dr[K] != null && dr[K].ToString() != string.Empty)
                             {
                                 hasValue = true;
                             }
                         }
                         if (hasValue)
                         {
                             dt.Rows.Add(dr);
                         }
                         j++;
                     }
                     ds.Tables.Add(dt);
                 }

                 return ds;
             }
         }

         /// <summary>
         /// 根据Excel返回DataSet
         /// </summary>
         /// <param name="filePath">Excel文件地址</param>
         /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
         /// <returns>DataSet</returns>
         public DataSet GetDataSet2(string filePath, string sheetname)
         {
             DataSet ds = new DataSet();
             using (FileStream fsRead = new FileStream(filePath, FileMode.Open, FileAccess.Read))
             {
                 IWorkbook fileWorkbook = null;
                 string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                 if (extension == ".xlsx" || extension == ".xls")
                 {
                     //判断excel的版本
                     if (extension == ".xlsx")
                     {
                         fileWorkbook = new XSSFWorkbook(fsRead);
                     }
                     else
                     {
                         fileWorkbook = new HSSFWorkbook(fsRead);
                     }
                 }

                 for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
                 {
                     //if (sheetname != null )
                     //    continue;
                     DataTable dt = new DataTable();
                     ISheet sheet = fileWorkbook.GetSheet(sheetname);

                     //表头
                     IRow header = sheet.GetRow(1);//sheet.FirstRowNum
                     List<int> columns = new List<int>();
                     for (int j = 0; j < 100; j++)
                     {
                         object obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell);
                         dt.Columns.Add(new DataColumn("Col" + j.ToString()));
                         //if (obj == null || obj.ToString() == string.Empty)
                         //{
                         //    dt.Columns.Add(new DataColumn("Col" + j.ToString()));
                         //}
                         //else
                         //    dt.Columns.Add(new DataColumn(obj.ToString()));
                         columns.Add(j);
                     }
                     //数据
                     //IEnumerator rows = sheet.GetEnumerator();
                     //while (rows.MoveNext())
                     //{
                     //    int j = sheet.FirstRowNum + 1;
                     //    DataRow dr = dt.NewRow();
                     //    bool hasValue = false;
                     //    foreach (int K in columns)
                     //    {
                     //        dr[K] = GetValueTypeForXLS(sheet.GetRow(K).GetCell(K) as HSSFCell);
                     //        if (dr[K] != null && dr[K].ToString() != string.Empty)
                     //        {
                     //            hasValue = true;
                     //        }
                     //    }
                     //    if (hasValue)
                     //    {
                     //        dt.Rows.Add(dr);
                     //    }
                     //    j++;
                     //}
                     //读取每行,从第二行起
                     for (int r = 0; r <= sheet.LastRowNum; r++)
                     {
                         bool result = false;
                         DataRow dr = dt.NewRow();
                         //获取当前行
                         IRow row = sheet.GetRow(r);
                         //读取每列
                         for (int j = 0; j < row.Cells.Count; j++)
                         {
                             ICell cell = row.GetCell(j); //一个单元格
                             dr[j] = GetCellValue(cell); //获取单元格的值
                             //全为空则不取
                             if (dr[j].ToString() != "")
                             {
                                 result = true;
                             }
                         }
                         if (result == true)
                         {
                             dt.Rows.Add(dr); //把每行追加到DataTable
                         }
                     }
                     ds.Tables.Add(dt);
                 }

                 return ds;
             }
         }
     

 /// <summary>
        /// 根据单元格将内容返回为对应类型的数据
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>数据</returns>
        private static object GetValueTypeForXLS(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        public string[] GetSheet(string filePath)
        { 
            HSSFWorkbook workbook;
            using (FileStream stream = File.OpenRead(filePath))
            {
                workbook = new HSSFWorkbook(stream);
            }
 
            int SheetCount= workbook.NumberOfSheets;//获取表的数量
            string[] SheetName = new string[SheetCount];//保存表的名称
            for (int i = 0; i < SheetCount; i++)
                SheetName[i] = workbook.GetSheetName(i);
            return SheetName;
        }

         //对单元格进行判断取值
         private static string GetCellValue(ICell cell)
         {
             if (cell == null)
                 return string.Empty;
             switch (cell.CellType)
             {
                 case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                     return string.Empty;
                 case CellType.Boolean: //bool类型
                     return cell.BooleanCellValue.ToString();
                 case CellType.Error:
                     return cell.ErrorCellValue.ToString();
                 case CellType.Numeric: //数字类型
                     if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                     {
                         return cell.DateCellValue.ToString();
                     }
                     else //其它数字
                     {
                         return cell.NumericCellValue.ToString();
                     }
                 case CellType.Unknown: //无法识别类型
                 default: //默认类型
                     return cell.ToString();//
                 case CellType.String: //string 类型
                     return cell.StringCellValue;
                 case CellType.Formula: //带公式类型
                     try
                     {
                         HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                         e.EvaluateInCell(cell);
                         return cell.ToString();
                     }
                     catch
                     {
                         return cell.NumericCellValue.ToString();
                     }
             }
         }








     }
